package com.softer.chengxin.person.dao.provider;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.person.domin.Person;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Gong Zhiyang
 * @date 2018/10/10
 */
public class PersonDaoProvider {
    /**
     * 查询企业从业人员信息数量
     *
     * @param map map
     * @return sql
     */
    public String queryPersonListNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        Integer departmentId = (Integer) map.get("departmentId");
        sql.append("SELECT count(*) FROM company_person");
        sql.append(" WHERE company_id = #{id} and duties not in('1010001','1010002','1010003','1010004') and project_id=0");
        commonSql(sql, departmentId, name);
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 查询企业从业人员信息列表
     *
     * @param map map
     * @return sql
     */
    public String queryPersonList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        Integer departmentId = (Integer) map.get("departmentId");
        String name = (String) map.get("name");
        sql.append("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties,state,");
        sql.append("project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday,");
        sql.append("country_code as countryCode,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone,");
        sql.append("political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major,insurance,certificate,id_card_file_id as idCardFileId,");
        sql.append("certificate_file_id as certificateFileId,social_security_file_id as socialSecurityFileId,");
        sql.append("labor_contract_file_id as laborContractFileId,diploma_file_id as diplomaFileId");
        sql.append(" FROM company_person WHERE company_id = #{id} and duties not in('1010001','1010002','1010003','1010004') and project_id=0");
        commonSql(sql, departmentId, name);
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        System.out.println(sql.toString());
        return sql.toString();
    }

    public void commonSql(StringBuffer sql, Integer departmentId, String name) {
        if (departmentId != null) {
            sql.append(" and department_id=#{departmentId}");
        }
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and instr(name,#{name})>0");
        }
    }

    /**
     * 查询企业所有人员，用于下拉列表
     *
     * @param map 姓名参数，用于模糊查询
     * @return 人员列表
     */
    public String queryAllPerson(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        sql.append("SELECT id,`name`,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties FROM company_person WHERE company_id=#{id} and state<>0");
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and instr(name,#{name})>0");
        }
        return sql.toString();
    }

    /**
     * 查询项目人员数量
     *
     * @param map 参数
     * @return SQL
     */
    public String queryProjectPersonNum(HashMap<String, Person> map) {
        StringBuffer sql = new StringBuffer(20);
        Person person = map.get("person");
        sql.append("SELECT count(*) FROM company_person");
        sql.append(" WHERE project_id = #{person.projectId} and state<>0");
        connectSql(person, sql);
        return sql.toString();
    }
    /**
     * 查询项目人员集合
     *
     * @param map 参数
     * @return SQL
     */
    public String queryProjectPersonList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Person person = (Person) map.get("person");
        Page page = (Page) map.get("page");
        sql.append("SELECT id,name,id_card as idCard,sex,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties,phone,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel  FROM company_person");
        sql.append(" WHERE project_id = #{person.projectId} and state<>0");
        connectSql(person, sql);
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    private void connectSql(Person person, StringBuffer sql) {
        if (StringUtils.isNotBlank(person.getName())) {
            sql.append(" and instr(name,#{person.name})>0");
        }
        if (StringUtils.isNotBlank(person.getIdCard())) {
            sql.append(" and instr(id_card,#{person.idCard})>0");
        }
        if (StringUtils.isNotBlank(person.getDuties())) {
            sql.append(" and duties=#{person.duties}");
        }
    }

}
